package com.zx.ziroom;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.annotation.JSONField;
import com.zx.common.C3P0Utils;

public class RoomStatisticsUtil {
	
	public static void main(String[] args) {
		String[] types = {"合", "整", "直"};
		List<ZiroomStatistics> result = new ArrayList<>();
		for (String type : types) {
			List<ZiroomStatistics> statistics = statistics(type);
			result.addAll(statistics);
		}
		//生成json
		for (ZiroomStatistics zhiLianStatistics : result) {
			System.out.println(JSONObject.toJSON(zhiLianStatistics).toString() + ",");
		}
	}
	
	/**
	 * @Title: 统计一个类型的数据
	 * @param type	整租、合租、直租
	 * @return
	 * @date: 2017年12月7日下午9:38:45
	 */
	private static List<ZiroomStatistics> statistics(String type) {
		Connection connection = C3P0Utils.getConnection();
		QueryRunner query = new QueryRunner();
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT");
		sql.append("	'"+type+"' type,"); 
		sql.append("	vi.room_type roomType,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='北京' AND z.price_type='合') bj, "); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='上海' AND z.price_type='合') sh,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='深圳' AND z.price_type='合') sz,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='杭州' AND z.price_type='合') hz,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='南京' AND z.price_type='合') nj,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='成都' AND z.price_type='合') cd,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='武汉' AND z.price_type='合') wh,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='广州' AND z.price_type='合') gz,"); 
		sql.append("	(SELECT ROUND(AVG(z.room_price))  FROM ziroom z WHERE z.room_type=vi.room_type AND z.city='天津' AND z.price_type='合') tj "); 
		sql.append("FROM (SELECT room_type FROM ziroom WHERE price_type='"+type+"' GROUP BY room_type) vi;"); 
		
		List<ZiroomStatistics> list = null;
		try {
			list = query.query(connection, sql.toString(), new BeanListHandler<ZiroomStatistics>(ZiroomStatistics.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static class ZiroomStatistics {
		private String type;//整租、合租、直租
		private String roomType;//一室一厅
		@JSONField(name = "北京")
		private Integer bj;
		
		@JSONField(name = "上海")
		private Integer sh;
		
		@JSONField(name = "深圳")
		private Integer sz;
		
		@JSONField(name = "杭州")
		private Integer hz;
		
		@JSONField(name = "南京")
		private Integer nj;
		
		@JSONField(name = "成都")
		private Integer cd;
		
		@JSONField(name = "武汉")
		private Integer wh;
		
		@JSONField(name = "广州")
		private Integer gz;
		
		@JSONField(name = "天津")
		private Integer tj;
		
		public ZiroomStatistics() {		}

		public ZiroomStatistics(String type, String roomType, Integer bj, Integer sh, Integer sz, Integer hz,
				Integer nj, Integer cd, Integer wh, Integer gz, Integer tj) {
			this.type = type;
			this.roomType = roomType;
			this.bj = bj;
			this.sh = sh;
			this.sz = sz;
			this.hz = hz;
			this.nj = nj;
			this.cd = cd;
			this.wh = wh;
			this.gz = gz;
			this.tj = tj;
		}

		public String getType() {
			return type;
		}

		public void setType(String type) {
			this.type = type;
		}

		public String getRoomType() {
			return roomType;
		}

		public void setRoomType(String roomType) {
			this.roomType = roomType;
		}

		public Integer getBj() {
			return bj == null ? 0 : bj;
		}

		public void setBj(Integer bj) {
			this.bj = bj;
		}

		public Integer getSh() {
			return sh == null ? 0 : sh;
		}

		public void setSh(Integer sh) {
			this.sh = sh;
		}

		public Integer getSz() {
			return sz == null ? 0 : sz;
		}

		public void setSz(Integer sz) {
			this.sz = sz;
		}

		public Integer getHz() {
			return hz == null ? 0 : hz;
		}

		public void setHz(Integer hz) {
			this.hz = hz;
		}

		public Integer getNj() {
			return nj == null ? 0 : nj;
		}

		public void setNj(Integer nj) {
			this.nj = nj;
		}

		public Integer getCd() {
			return cd == null ? 0 : cd;
		}

		public void setCd(Integer cd) {
			this.cd = cd;
		}

		public Integer getWh() {
			return wh == null ? 0 : wh;
		}

		public void setWh(Integer wh) {
			this.wh = wh;
		}

		public Integer getGz() {
			return gz == null ? 0 : gz;
		}

		public void setGz(Integer gz) {
			this.gz = gz;
		}

		public Integer getTj() {
			return tj == null ? 0 : tj;
		}

		public void setTj(Integer tj) {
			this.tj = tj;
		}

		@Override
		public String toString() {
			return "ZiroomStatistics [type=" + type + ", roomType=" + roomType + ", bj=" + bj + ", sh=" + sh + ", sz="
					+ sz + ", hz=" + hz + ", nj=" + nj + ", cd=" + cd + ", wh=" + wh + ", gz=" + gz + ", tj=" + tj
					+ "]";
		}
		
	}
	
}
